package com.guhappy.util;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collection;
import java.util.List;
import java.util.Map;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.struts2.ServletActionContext;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;

/**
 * Home object for domain model class CmnIngresstrunks.
 * @see .CmnIngresstrunks
 * @author Hibernate Tools
 */
public class CommonHome {

	private static final Log log = LogFactory
			.getLog(CommonHome.class);
	private static final SessionFactory sessionFactory = getSessionFactory();

	protected static SessionFactory getSessionFactory() {
		SessionFactory sessionFactory = 
	         (SessionFactory) ServletActionContext.getServletContext()
                    .getAttribute(HibernateListener.KEY_NAME);
		return sessionFactory;
	}

	public static void setCustomerOnlyMap(Map<String, String> customerMap) throws SQLException, Exception{
		Connection conn = null;
		Statement stmt=null; 
		ResultSet rset;
		Context initContext = new InitialContext();
   		Context envContext  = (Context)initContext.lookup("java:/comp/env");
   		DataSource ds = (DataSource)envContext.lookup("jdbc/myoracle");
   		conn = ds.getConnection("CINBELLINP", "pulse");
   		stmt= conn.createStatement();
   		rset = stmt.executeQuery ("SELECT id, name FROM CMN_CUSTOMERS order by name");
    	
		while (rset.next()){
			customerMap.put(rset.getString(1), rset.getString(2));
		}
	}
	
	public static void setSwitchOnlyMap(Map<String, String> switchMap) throws SQLException, Exception{
		Connection conn = null;
		Statement stmt=null; 
		ResultSet rset;
		Context initContext = new InitialContext();
   		Context envContext  = (Context)initContext.lookup("java:/comp/env");
   		DataSource ds = (DataSource)envContext.lookup("jdbc/myoracle");
   		conn = ds.getConnection("CINBELLINP", "pulse");
   		stmt= conn.createStatement();
   		rset = stmt.executeQuery ("SELECT id, name FROM CMN_SWITCHES order by name");
    	
		while (rset.next()){
			switchMap.put(rset.getString(1), rset.getString(2));
		}
	}
	public static void setSupplierOnlyMap(Map<String, String> supplierMap) throws SQLException, Exception{
		Connection conn = null;
		Statement stmt=null; 
		ResultSet rset;
		Context initContext = new InitialContext();
   		Context envContext  = (Context)initContext.lookup("java:/comp/env");
   		DataSource ds = (DataSource)envContext.lookup("jdbc/myoracle");
   		conn = ds.getConnection("CINBELLINP", "pulse");
   		stmt= conn.createStatement();
   		rset = stmt.executeQuery ("SELECT id, name FROM CMN_SUPPLIERS order by name");
		while (rset.next()){
			supplierMap.put(rset.getString(1), rset.getString(2));
		}
	}
	
	public static void setSupplierNotOnMap(String blackgroupid,Map<String, String> supplierMap) throws SQLException, Exception{
		Connection conn = null;
		Statement stmt=null; 
		ResultSet rset;
		StringBuffer sql=new StringBuffer("SELECT id, name FROM CMN_SUPPLIERS A where NOT EXISTS (SELECT * FROM CMN_SUPPLIERBLOCKBYGROUP B WHERE A.ID=B.SUPPLIERID AND B.BLACKGROUPID LIKE '");
		sql.append(blackgroupid).append("%'");
		sql.append(") order by name");
		Context initContext = new InitialContext();
   		Context envContext  = (Context)initContext.lookup("java:/comp/env");
   		DataSource ds = (DataSource)envContext.lookup("jdbc/myoracle");
   		conn = ds.getConnection("CINBELLINP", "pulse");
   		stmt= conn.createStatement();
   		rset = stmt.executeQuery (sql.toString());
		while (rset.next()){
			supplierMap.put(rset.getString(1), rset.getString(2));
		}
	}
	public static Collection buildListSwitch(){
		Session session=null;
		List resultList=null;
		try{
		session = sessionFactory.getCurrentSession();
		Query q = session.createQuery("select new com.pulse.ipmanager.switches.CmnSwitches(a.id, a.name) from com.pulse.ipmanager.switches.CmnSwitches a order by a.name asc");
		resultList=q.list();
		}
		catch(Exception e){
			log.error("buildList failed", e);
		}
		return resultList;
	}
	
	public static Collection buildListRouteLabel(){
		List resultList = null;
		Session s= null;
		s = sessionFactory.getCurrentSession();
		Query q = s.createQuery("select name from com.pulse.ipmanager.egress.CmnRoutelabels order by name");
		resultList=q.list();
		
		return resultList ;
	}
	
	public static void setPrgrNotOnMap(Map<String, String> prgrMap) throws SQLException, Exception{
		Connection conn = null;
		Statement stmt=null; 
		ResultSet rset;
		StringBuffer sql=new StringBuffer("SELECT id, groupname FROM CMN_PRICEGROUPS order by groupname");
		Context initContext = new InitialContext();
   		Context envContext  = (Context)initContext.lookup("java:/comp/env");
   		DataSource ds = (DataSource)envContext.lookup("jdbc/myoracle");
   		conn = ds.getConnection("CINBELLINP", "pulse");
   		stmt= conn.createStatement();
   		rset = stmt.executeQuery (sql.toString());
		while (rset.next()){
			prgrMap.put(rset.getString(1), rset.getString(2));
		}
	}
	
	public static void setBlksupNotOnMap(Map<String, String> blksupMap) throws SQLException, Exception{
		Connection conn = null;
		Statement stmt=null; 
		ResultSet rset;
		StringBuffer sql=new StringBuffer("SELECT id, name FROM cmn_blackgroups order by name");
		Context initContext = new InitialContext();
   		Context envContext  = (Context)initContext.lookup("java:/comp/env");
   		DataSource ds = (DataSource)envContext.lookup("jdbc/myoracle");
   		conn = ds.getConnection("CINBELLINP", "pulse");
   		stmt= conn.createStatement();
   		rset = stmt.executeQuery (sql.toString());
		while (rset.next()){
			blksupMap.put(rset.getString(1), rset.getString(2));
		}
	}
}
